﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace _1142063_1142080_1142098.Models.Services
{
    public class MyDataProvider
    {
        private Configuration rootWebConfig = WebConfigurationManager.OpenWebConfiguration("/1142063_1142080_1142098");
        private string CONSTRINGNAME = "connectionString";
        public SqlConnection sqlConnection;

        public SqlConnection Open()
        {
            ConnectionStringSettings conString = rootWebConfig.ConnectionStrings.ConnectionStrings[CONSTRINGNAME];
            sqlConnection = new SqlConnection(conString.ConnectionString);
            sqlConnection.Open();
            return sqlConnection;
        }

        public void Close()
        {
            sqlConnection.Close();
        }

        public DataTable ExcuteQueryWithParameters(string storeName, List<SqlParameter> parameterList=null,SqlTransaction sqlTransaction=null)
        {
            var dt = new DataTable();
            var adap = new SqlDataAdapter(storeName, sqlConnection);
            adap.SelectCommand.CommandType = CommandType.StoredProcedure;
            adap.SelectCommand.CommandTimeout = 0;
            if (sqlTransaction != null)
            {
                adap.SelectCommand.Transaction = sqlTransaction;
            }
            if (parameterList!=null)
            {
                foreach (var parameter in parameterList)
                {
                    adap.SelectCommand.Parameters.Add(parameter);
                }
            }
            adap.Fill(dt);
            adap.SelectCommand.Parameters.Clear();
            return dt;
        }

        public object ExcuteScalarWithParameters(string storeName, List<SqlParameter> parameterList = null, SqlTransaction sqlTransaction = null)
        {
            var adap = new SqlDataAdapter(storeName, sqlConnection);
            adap.SelectCommand.CommandType = CommandType.StoredProcedure;
            adap.SelectCommand.CommandTimeout = 0;
            if (sqlTransaction != null)
            {
                adap.SelectCommand.Transaction = sqlTransaction;
            }
            if (parameterList != null)
            {
                foreach (var parameter in parameterList)
                {
                    adap.SelectCommand.Parameters.Add(parameter);
                }
            }
            var kq=adap.SelectCommand.ExecuteScalar();
            adap.SelectCommand.Parameters.Clear();
            return kq;
        }
    }
}